﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using Core.DatabaseHelpers;
using Core.Interfaces;

namespace Core.Connections
{
    class MsSqlConnection : IConnection
    {
        private SqlConnection _connection;


        private string _serverurl;
        private string _databaseName;
        private string _user;
        private string _password;
        private string _errormessage;

        public MsSqlConnection(string serverUrl, string databaseName, string user, string password)
        {
            _serverurl = serverUrl;
            _databaseName = databaseName;
            _user = user;
            _password = password;

            _connection = new SqlConnection("Data Source=" + serverUrl + ";Initial Catalog=" + databaseName + ";Integrated Security=True");
        }

        public void ConfigureConnection(string serverUrl, string databaseName, string user, string password)
        {
            _serverurl = serverUrl;
            _databaseName = databaseName;
            _user = user;
            _password = password;
            _connection = new SqlConnection("SERVER=" + _serverurl + ";" + "DATABASE=" + _databaseName + ";" + "UID=" + _user + ";" + "PASSWORD=" + _password + ";");
        }

        public string ServerUrl
        {
            get { return _serverurl; }
        }

        public string DatabaseName
        {
            get { return _databaseName; }
        }

        public bool OpenConnection()
        {
            try
            {
                _connection.Open();
                return true;
            }
            catch (Exception ex)
            {
                _errormessage = ex.Message;
                return false;
            }
        }

        public IEnumerable<object[]> ExecuteSelectQuery<T>(string query, bool isLazy)
        {
            var result = new List<object[]>();
            _connection.Open();
            var cmd = new SqlCommand(query, _connection);

            SqlDataReader dataReader = cmd.ExecuteReader();
            while (dataReader.Read())
            {
                object[] row = new object[ColumnHelper.ColumnCounter<T>(isLazy)];
                dataReader.GetValues(row);
                result.Add(row);
            }
            _connection.Close();
            return result;
        }

        public IEnumerable<object[]> ExecuteSelectQuery(string query, byte colcount)
        {
            var result = new List<object[]>();
            _connection.Open();
            var cmd = new SqlCommand(query, _connection);

            SqlDataReader dataReader = cmd.ExecuteReader();
            while (dataReader.Read())
            {
                object[] row = new object[colcount];
                dataReader.GetValues(row);
                result.Add(row);
            }
            _connection.Close();
            return result;
        }

        public void CloseConnection()
        {
            _connection.Close();
        }


        /// <summary>
        /// Сконструировать SELECT запрос к базе данных
        /// </summary>
        /// <param name="tableName">Имя таблици, к которой осуществляется запрос</param>
        /// <param name="parameters">Параметры для блока WHERE</param>
        /// <returns></returns>
        public string SelectQuery<T>(string tableName, string parameters, bool isLazy = false)
        {

            return parameters == ""
                       ? "select " + ColumnHelper.GetListOfColumns<T>(isLazy) + " from " + tableName
                       : "select " + ColumnHelper.GetListOfColumns<T>(isLazy) + " from " + tableName + " where " + parameters;
        }

        /// <summary>
        /// Сконструировать INSERT запрос к базе
        /// </summary>
        /// <param name="tableName">Имя таблици, к которой осуществляется запрос</param>
        /// <param name="values">Массив параметров запроса</param>
        /// <returns>SQL запрос</returns>
        public string InsertQuery(string tableName, Dictionary<string, string> values)
        {

            var result = "insert into " + tableName + " (";
            result = values.Aggregate(result, (current, keyValuePair) => current + (keyValuePair.Key + ","));
            result = result.Substring(0, result.Length - 1) + ") values(";
            result = values.Aggregate(result, (current, keyValuePair) => current + (keyValuePair.Value + ","));
            return result.Substring(0, result.Length - 1) + ")";
        }

        public string UpdateQuery(string tableName, IEnumerable<KeyValuePair<string, string>> columnValuePair, string whereStatement)
        {
            var result = "UPDATE " + tableName + " SET ";
            result = columnValuePair.Aggregate(result, (current, value) => current + (value.Key + "=" + value.Value + ","));
            result = result.Substring(0, result.Length - 1);
            return whereStatement == ""
                      ? result
                      : result + " where " + whereStatement;
        }
        /// <summary>
        /// Создание схемы базы данных
        /// </summary>
        /// <param name="nameOfSchema">Имя схемы</param>
        public void CreateSchema(string nameOfSchema)
        {
            var schemaBuildingQuery = "";
            _connection = new SqlConnection("SERVER=" + _serverurl + ";" + "DATABASE=" + "mysql" + ";" + "UID=" + _user + ";" + "PASSWORD=" + _password + ";");
            //schemaBuildingQuery = Resources.mysql;
            ExecuteQuery("DROP SCHEMA  IF EXISTS `" + nameOfSchema + "` ;");
            ExecuteQuery("CREATE SCHEMA `" + nameOfSchema + "` ;");
            _connection = new SqlConnection("SERVER=" + _serverurl + ";" + "DATABASE=" + nameOfSchema + ";" + "UID=" + _user + ";" + "PASSWORD=" + _password + ";");
            ExecuteQuery(schemaBuildingQuery);

        }

        /// <summary>
        /// Выполнить SQL комманду
        /// </summary>
        /// <param name="query">Текст комманды</param>
        public void ExecuteQuery(string query)
        {
            _connection.Open();
            var cmd = new SqlCommand(query, _connection);
            cmd.ExecuteNonQuery();
            _connection.Close();
        }

        public string DeleteQuery(string TableName, List<KeyValuePair<string, string>> pk)
        {
            var result = "delete from " + TableName + " where ";
            result = pk.Aggregate(result, (current, keyValuePair) => current + (keyValuePair.Key + "=" + keyValuePair.Value + " AND "));
            return result.Substring(0, result.Length - 4);
        }
    }
}
